﻿using System.Collections.Generic;
using System;
using System.Text;
using System.Data.SqlClient;
namespace MotoreDataBase
{
    public class ClassRow
    {
        public string Nome { get; set; }
        public int MyProperty { get; set; }
    }

    public class CreateDataBase
    {
        public static string stringConnection = string.Empty;

        public static void CreaTabella<T>()
        {
            StringBuilder query = new StringBuilder();
            TableDefinition tableDefinition;
            List<T> lista = new List<T>();
            Type tipo = typeof(T);
            var listNome = tipo.GetCustomAttributes(typeof(Nome), false);
            if (listNome.Length > 0)
            {
                query.Append(string.Format("CREATE TABLE {0} ( ", listNome[0].ToString()));
                System.Reflection.PropertyInfo[] propList = tipo.GetProperties();
                int index = 0;
                foreach (var item in propList)
                {
                    var attrNome = item.GetCustomAttributes(typeof(Nome), false);
                    var attrTableDefinition = item.GetCustomAttributes(typeof(TableDefinition), false);

                    if (attrNome.Length > 0 && attrTableDefinition.Length > 0)
                    {
                        tableDefinition = (TableDefinition)attrTableDefinition[0];
                        query.Append(attrNome[0].ToString());
                        query.Append(" ");
                        query.Append(tableDefinition.type.GetStringValue());
                        switch(tableDefinition.type)
                        {
                            case TipoDatiDb.VARCHAR:
                            case TipoDatiDb.VARBINARY:
                            case TipoDatiDb.NCHAR:
                            case TipoDatiDb.BINARY:
                            case TipoDatiDb.CHAR:
                            case TipoDatiDb.DATETIME2:
                            case TipoDatiDb.DATETIMEOFFSET:
                            case TipoDatiDb.NVARCHAR:
                                if (tableDefinition.length > 0)
                                {
                                    query.AppendFormat(" ({0})", tableDefinition.length.ToString());
                                }
                                else
                                    throw new MotoreDbException.LengthRequiredException();
                                break;
                        }
                        
                        if (!tableDefinition.allowNull)
                            query.Append(" NOT NULL ");
                        if(tableDefinition.pK)
                            query.Append("PRIMARY KEY ");
                        if(tableDefinition.autoIncrement)
                            query.Append("IDENTITY ");
                        if (index < propList.Length - 1)
                            query.Append(",");
                        index++;

                    }
                }
                query.Append(")");
            }
            using (SqlConnection SqlCnn = new SqlConnection(stringConnection))
            {
                SqlCnn.Open();
                using (SqlCommand SqlCmd = SqlCnn.CreateCommand())
                {
                    SqlCmd.CommandText = query.ToString();
                    int n = SqlCmd.ExecuteNonQuery();
                }
            }
        }

        public static bool TableExist<T>()
        {
            string query = string.Empty;
            List<T> lista = new List<T>();
            Type tipo = typeof(T);
            var listNome = tipo.GetCustomAttributes(typeof(Nome), false);
            if (listNome.Length > 0)
            {
                query = string.Format("SELECT COUNT (*) FROM information_schema.tables WHERE table_name = '{0}'", listNome[0]);
                using (SqlConnection SqlCnn = new SqlConnection(stringConnection))
                {
                    SqlCnn.Open();
                    using (SqlCommand SqlCmd = SqlCnn.CreateCommand())
                    {
                        SqlCmd.CommandText = query;
                        if ((int)SqlCmd.ExecuteScalar() > 0)
                            return true;
                    }
                }
            }
            return false;
        }

        public static bool DeleteTable<T>()
        {
            string query = string.Empty;
            List<T> lista = new List<T>();
            Type tipo = typeof(T);
            var listNome = tipo.GetCustomAttributes(typeof(Nome), false);
            if (listNome.Length > 0)
            {
                query = string.Format("DROP TABLE {0}", listNome[0]);
                using (SqlConnection SqlCnn = new SqlConnection(stringConnection))
                {
                    SqlCnn.Open();
                    using (SqlCommand SqlCmd = SqlCnn.CreateCommand())
                    {
                        SqlCmd.CommandText = query;
                        try
                        {
                            SqlCmd.ExecuteScalar();
                            return true;
                        }
                        catch { }
                    }
                }
            }

            return false;
        }
    }

    
}